package com.fsnbigdata.repository;
import com.fsnbigdata.domain.PlantConservation;
import com.fsnbigdata.domain.dto.NamingPair2;
import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.Query;
import org.springframework.roo.addon.layers.repository.jpa.annotations.RooJpaRepository;
import io.springlets.data.jpa.repository.DetachableJpaRepository;
import org.springframework.transaction.annotation.Transactional;

/**
 * = PlantConservationRepository
 *
 * TODO Auto-generated class documentation
 *
 */
@RooJpaRepository(entity = PlantConservation.class)
@Transactional(readOnly = true)
public interface PlantConservationRepository extends DetachableJpaRepository<PlantConservation, Long>, PlantConservationRepositoryCustom {

    List<NamingPair2> findAllByInitial(String initial);

    Page<PlantConservation> findAll(Specification<PlantConservation> specification, Pageable pageable1);
    @Query(value = "select id,name from plant_conservation where initial=?1",nativeQuery = true)
    List<Object> queryByAlphabet(String s);

    @Query(value = "select id,name,latin,creator,initial,family,genus,species from plant_conservation where if(?1 !='',name like concat('%',?1,'%'),1=1) AND if(?2 !='',latin like concat('%',?2,'%'),1=1) ORDER BY ?#{#pageable}",
            countQuery = "select count(*) from plant_conservation where if(?1 !='',name like concat('%',?1,'%'),1=1) AND if(?2 !='',latin like concat('%',?2,'%'),1=1)",nativeQuery = true)
    List<Object> queryAllByKey(String name,String latin, Pageable pageable);

    @Query(value = "select count(*) from plant_conservation where if(?1 !='',name like concat('%',?1,'%'),1=1) AND if(?2 !='',latin like concat('%',?2,'%'),1=1)",nativeQuery = true)
    Long queryCountByKey(String name,String latin);

    @Query(value = "select * from plant_conservation",nativeQuery = true)
    List<Object> getCount();

    @Query(value = "SELECT * FROM plant_conservation limit 4",nativeQuery = true)
    List<PlantConservation> queryLimit();
}
